﻿
namespace DatabaseCore.Dao
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Collections.Generic;
    using System.Transactions;

    /// <summary>
    /// The sql notifier.
    /// </summary>
    public class SQLNotifier : IDisposable
    {

        private SqlTransaction sqlTransaction;
        public SqlCommand CurrentCommand { get; set; }
        private SqlConnection connection;
        public SqlConnection CurrentConnection
        {
            get
            {
                this.connection = this.connection ?? new SqlConnection(this.ConnectionString);
                return this.connection;
            }
        }

        public string ConnectionString
        {
            get
            {
                //return @"Data Source=172.31.195.6;Initial Catalog=exportdb;Persist Security Info=True;User ID=consohadmin;Password=P@ssw0rd";
                return @"Data Source=172.31.195.6;Initial Catalog=exportdb;Persist Security Info=True;User ID=consohadmin;Password=P@ssw0rd";
                //return @"Data Source=.;Initial Catalog=exportDb;persist security info=True;user id=exportadmin;password=pclocl;";
            }
        }

        public SQLNotifier()
        {
            SqlDependency.Start(this.ConnectionString);

        }
        private event EventHandler<SqlNotificationEventArgs> _newMessage;

        public event EventHandler<SqlNotificationEventArgs> NewMessage
        {
            add
            {
                this._newMessage += value;
            }
            remove
            {
                this._newMessage -= value;
            }
        }

        public virtual void OnNewMessage(SqlNotificationEventArgs notification)
        {
            if (this._newMessage != null)
                this._newMessage(this, notification);
        }

        public DataTable RegisterDependency(bool isRegisterOnly = false)
        {
            string SqlQuery = @"SELECT TOP 10 [PalletDetailID]
                                  ,[PalletID]
                                  ,[ItemNo]
                                  ,[ProductCode]
                                  ,[Lot]
                                  ,[MachineCode]
                                  ,[ProductQty]
                                  ,[PutawayPlanQty]
                                  ,[PutawayQty]
                                  ,[UOM]
                                  ,[BaseUnitQty]
                                  ,[BaseUnit]
                                  ,[CompanyCode]
                                  ,[PlantCode]
                                  ,[PlantDestination]
                                  ,[CostCenter]
                                  ,[MachineRecipient]
                                  ,[BarcodeTag2]
                                  ,[BarcodeTag3]
                                  ,[GIOrderNo]
                                  ,[CreateBy]
                                  ,[CreateDatetime]
                                  ,[MovementType]
                                  ,[IsUsed]
                              FROM [dbo].[ProductMovementTemp]
                              WHERE [IsUsed] = 0
                                AND [PlantCode] IS NOT NULL 
                                AND [MovementType] IS NOT NULL ";
            //AND [PlantCode] IS NOT NULL and CreateDatetime > dateadd(minute, -10, GetDate());";

            //            string SqlQuery = @"SELECT Q.[id]
            //                                    ,Q.[ProductCode]
            //                                    ,Q.[PlantCode]
            //                                    ,Q.[OrderNo]
            //                                    ,Q.[Qty]
            //                                    ,Q.[Date]
            //                                    ,Q.[Priority]
            //                                    ,Q.[Rate]
            //                                    ,Q.[ValidFrom]
            //                                    ,Q.[ValidTo]
            //                                    ,Q.[IsActive]
            //                                    ,D.[ItemId] AS [ProductName]
            //                                    ,O.[RefOrderNo]
            //                                    ,D.[Qty] AS [OriginalQty]
            //                            FROM [dbo].[ProdQueue] Q
            //                            ";

            CurrentCommand = new SqlCommand(SqlQuery, CurrentConnection);
            this.CurrentCommand.Notification = null;

            SqlDependency dependency = new SqlDependency(this.CurrentCommand);
            dependency.OnChange += this.dependency_OnChange;

            if (this.CurrentConnection.State == ConnectionState.Closed)
                this.CurrentConnection.Open();
            try
            {
                DataTable dt = new DataTable();
                dt.Load(this.CurrentCommand.ExecuteReader());  //CommandBehavior.CloseConnection

                if (isRegisterOnly)
                {
                    this.CurrentConnection.Close();
                    return dt;
                }

                //new connection async to update IsUsed immediately after select
                List<decimal> productUseds = new List<decimal>();
                foreach (DataRow row in dt.Rows)
                {
                    productUseds.Add(Convert.ToDecimal(row["PalletDetailID"]));
                }

                //UpdateIsUsedImmediately(productUseds);

                return dt;
            }
            catch { return null; }
        }

        void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;

            dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);

            this.OnNewMessage(e);
        }

        public void Insert(string msgTitle, string description)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("usp_CreateMessage", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    con.Open();

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
        }

        // Remove
        public void UpdateIsUsed(List<decimal> palletDetailIDs)
        {
            string sql = "";
            foreach (var palletDetailID in palletDetailIDs)
            {
                sql += string.Format("UPDATE [dbo].[ProductMovementTemp] SET [IsUsed] = 1 WHERE [PalletDetailID] = {0}", palletDetailID);
            }

            using (SqlCommand cmd = new SqlCommand(sql, CurrentConnection))
            {
                cmd.CommandType = CommandType.Text;

                if (CurrentConnection.State == ConnectionState.Closed)
                    CurrentConnection.Open();

                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    //UpdateIsUsed น่าจะทำหลังสุดแล้วค่อย close, ถ้าเคสนี้อาจจะต้องรอตอบจาก Sp_CalProductQueue ก่อนว่าโอนสำเร็จหรือเปล่า
                    CurrentConnection.Close();
                }
            }

            #region bak
            //using (SqlConnection con = new SqlConnection(this.ConnectionString))
            //{
            //    string sql = "";
            //    foreach (var palletDetailID in palletDetailIDs)
            //    {
            //        sql += string.Format("UPDATE [dbo].[ProductMovementTemp] SET [IsUsed] = 1 WHERE [PalletDetailID] = {0}", palletDetailID);
            //    }

            //    using (SqlCommand cmd = new SqlCommand(sql, con))
            //    {
            //        cmd.CommandType = CommandType.Text;
            //        con.Open();
            //        try
            //        {
            //            cmd.ExecuteNonQuery();
            //        }
            //        finally
            //        {
            //            con.Close();
            //        }
            //    }
            //}
            #endregion
        }

        public string CallSp_CalProductQueue(decimal palletDetailID, string materialNo, string plant, decimal qty,
            out string materialNoOut, out string plantOut, out decimal qtyReceivedOut, out decimal palletDetailIdOut)
        {
            

            materialNoOut = null;
            plantOut = null;
            qtyReceivedOut = 0;
            palletDetailIdOut = 0;
            if (sqlTransaction != null)
            {
                return "cancel";
                
            }

               string sql = "[dbo].[CalProductQueue]";
            using (SqlCommand cmd = new SqlCommand(sql, CurrentConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@PalletDetailId", SqlDbType.Decimal).Value = palletDetailID;
                cmd.Parameters.Add("@productIdMoved", SqlDbType.NText).Value = materialNo;
                cmd.Parameters.Add("@PlantCode", SqlDbType.NText).Value = plant;
                cmd.Parameters.Add("@QtyMove", SqlDbType.Decimal).Value = qty;

                //output
                cmd.Parameters.Add("@MaterialNoOut", SqlDbType.NVarChar, 25).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@PlantOut", SqlDbType.NVarChar, 4).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@QtyReceivedOut", SqlDbType.Decimal).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@PalletDetailIdOut", SqlDbType.Decimal).Direction = ParameterDirection.Output;

                if (CurrentConnection.State == ConnectionState.Closed)
                    CurrentConnection.Open();
                try
                {
                    
                    using (var ts = new TransactionScope())
                    {
                        sqlTransaction = CurrentConnection.BeginTransaction();
                        cmd.Transaction = sqlTransaction;

                        cmd.ExecuteNonQuery();

                        //TODO - ยังไม่เทสว่า มีผลลัพธ์คืนค่ามาไหม เพราะยังไม่ commit


                        //materialNoOut = cmd.Parameters["@MaterialNoOut"].Value.ToString();
                        //plantOut = cmd.Parameters["@PlantOut"].Value.ToString();
                        //object k = cmd.Parameters["@QtyReceivedOut"].Value;
                        //object k2  = cmd.Parameters["@PalletDetailIdOut"].Value;

                        //TODO - ถ้า call sap ไม่สำเร็จ ก็ให้ rollback ที่ทำใน procedure, จะได้ไม่ต้องสร้าง revert procedure ใหม่
                        //TODO - ยังไม่ได้ test กับฟังก์ชั่นนี้, test กับ TestConnection แล้วผ่าน
                        //bool isSuccess = CallWebService(materialNoOut, plantOut, qtyReceivedOut, palletDetailIdOut);
                        //if (!isSuccess)
                        //{
                        //    sqlTransaction.Rollback();
                        //    return;
                        //}

                        sqlTransaction.Commit();
                        sqlTransaction = null;
                    }
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }

                finally
                {
                    sqlTransaction = null;
                    //CurrentConnection.Close();
                }
                return "succes";
            }

            #region Bak
            //using (SqlConnection con = new SqlConnection(this.ConnectionString))
            //{
            //    string sql = "[dbo].[CalProductQueue]";
            //    using (SqlCommand cmd = new SqlCommand(sql, con))
            //    {
            //        cmd.CommandType = CommandType.StoredProcedure;

            //        cmd.Parameters.Add("@PalletDetailId", SqlDbType.Decimal).Value = palletDetailID;
            //        cmd.Parameters.Add("@productIdMoved", SqlDbType.NText).Value = materialNo;
            //        cmd.Parameters.Add("@PlantCode", SqlDbType.NText).Value = plant;
            //        cmd.Parameters.Add("@QtyMove", SqlDbType.Decimal).Value = qty;

            //        con.Open();
            //        try
            //        {
            //            cmd.ExecuteNonQuery();
            //        }
            //        finally
            //        {
            //            con.Close();
            //        }
            //    }
            //}
            #endregion
        }

        // TEST
        // มีแก้ store usp_PalletCreatePalletID เองด้วย ไม่ให้ใช้ข้อมูล PalletData เพราะไม่มีข้อมูลนี้ มันจะเกิด errror
        // แก้เป็นใส่ insert เองสัก 2 rows
        // DatabaseCore/Scripts/Procedure/Consoh_uspPalletCreatePalletID.sql
        public void CallSp_PalletCreatePalletID(out string palletID, out string errorCode, out string errorMessage)
        {
            palletID = null;
            errorCode = null;
            errorMessage = null;

            string ConsohConnectionString = @"Data Source=.;Initial Catalog=DBConsohTest;persist security info=True;user id=exportadmin;password=pclocl;";

            using (SqlConnection con = new SqlConnection(ConsohConnectionString))
            {
                string sql = "[dbo].[usp_PalletCreatePalletID]";
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@WHSource", SqlDbType.NVarChar).Value = "331";
                    cmd.Parameters.Add("@WHDestination", SqlDbType.NVarChar).Value = "211";
                    cmd.Parameters.Add("@WorkType", SqlDbType.NVarChar).Value = "GR";
                    cmd.Parameters.Add("@CreateForCode", SqlDbType.NVarChar).Value = "2";
                    cmd.Parameters.Add("@MovementType", SqlDbType.NVarChar).Value = "301";
                    cmd.Parameters.Add("@ProductionDate", SqlDbType.DateTime).Value = DateTime.Now;
                    cmd.Parameters.Add("@TeamCode", SqlDbType.NVarChar).Value = "A";
                    cmd.Parameters.Add("@ShiftCode", SqlDbType.NVarChar).Value = "2";
                    cmd.Parameters.Add("@PalletData", SqlDbType.NVarChar).Value = @"<PalletData><PalletData> <ID>1</ID> <MachineCode>1111</MachineCode> <Qty>11</Qty> <Lot>2222</Lot>   <ProductCode>ZZ1100700405100100</ProductCode> <ProductionData>555555555</ProductionData>  </PalletData>
<PalletData> <ID>1</ID> <MachineCode>1111</MachineCode> <Qty>11</Qty> <Lot>2222</Lot>   <ProductCode>ZZ1100700405100100</ProductCode> <ProductionData>555555555</ProductionData>  </PalletData>
<PalletData> <ID>1</ID> <MachineCode>2222</MachineCode> <Qty>22</Qty> <Lot>2222</Lot>   <ProductCode>ZZ1100700405100100</ProductCode> <ProductionData>555555555</ProductionData>  </PalletData>
<PalletData> <ID>1</ID> <MachineCode>3333</MachineCode> <Qty>33</Qty> <Lot>2222</Lot>   <ProductCode>ZZ1100700405100100</ProductCode> <ProductionData>555555555</ProductionData>  </PalletData>
<PalletData> <ID>1</ID> <MachineCode>4444</MachineCode> <Qty>44</Qty> <Lot>2222</Lot>   <ProductCode>ZZ1100700405100100</ProductCode> <ProductionData>555555555</ProductionData>  </PalletData>
<PalletData> <ID>1</ID> <MachineCode>5555</MachineCode> <Qty>55</Qty> <Lot>2222</Lot>   <ProductCode>ZZ1100700405100100</ProductCode> <ProductionData>555555555</ProductionData>  </PalletData>
</PalletData>";
                    cmd.Parameters.Add("@UOM", SqlDbType.NVarChar).Value = "BOX";
                    cmd.Parameters.Add("@CompanyCode", SqlDbType.NVarChar).Value = "0870";
                    cmd.Parameters.Add("@User", SqlDbType.Int).Value = 11;

                    cmd.Parameters.Add("@PalletID", SqlDbType.NVarChar, 25).Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@ERROR_CODE", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@ERROR_MESSAGE", SqlDbType.NVarChar, 1000).Direction = ParameterDirection.Output;

                    con.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();

                        palletID = cmd.Parameters["@PalletID"].Value.ToString();
                        errorCode = cmd.Parameters["@ERROR_CODE"].Value.ToString();
                        errorMessage = cmd.Parameters["@ERROR_MESSAGE"].Value.ToString();
                    }
                    catch (Exception ex)
                    {
                        errorMessage = ex.Message;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
        }

        // TEST
        // จำลองการเรียก store procedure, web service และใช้เวลา process นาน
        public void CallSp_WaitForDelay()
        {
            string sql = "WAITFOR DELAY '0:0:10';";
            using (SqlCommand cmd = new SqlCommand(sql, CurrentConnection))
            {
                cmd.CommandType = CommandType.Text;

                if (CurrentConnection.State == ConnectionState.Closed)
                    CurrentConnection.Open();

                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {

                }
            }
        }

        private SqlConnection updateConnection;

        public void UpdateIsUsedImmediately(List<decimal> palletDetailIDs)
        {
            string sql = "";
            foreach (var palletDetailID in palletDetailIDs)
            {
                sql += string.Format("UPDATE [dbo].[ProductMovementTemp] SET [IsUsed] = 1 WHERE [PalletDetailID] = {0}", palletDetailID);
            }

            updateConnection = new SqlConnection(ConnectionString);
            using (SqlCommand cmd = new SqlCommand(sql, updateConnection))
            {
                cmd.CommandType = CommandType.Text;

                updateConnection.Open();
                try
                {
                    AsyncCallback callback = new AsyncCallback(HandleCallback);
                    cmd.BeginExecuteNonQuery(callback, cmd);
                }
                catch (Exception ex)
                {
                    if (updateConnection != null)
                    {
                        updateConnection.Close();
                    }
                }
            }
        }

        private void HandleCallback(IAsyncResult result)
        {
            try
            {
                SqlCommand command = (SqlCommand)result.AsyncState;
                int rowCount = command.EndExecuteNonQuery(result);
            }
            finally
            {
                if (updateConnection != null)
                {
                    updateConnection.Close();
                }
            }
        }

        //TEST
        public void UpdatePlant()
        {
            string sql = "UPDATE [dbo].[ProductMovementTemp] SET [PlantCode] = '2811'";

            using (var innerConnection = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, innerConnection))
                {
                    cmd.CommandType = CommandType.Text;

                    innerConnection.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
        }

        //TEST
        public void TestConnection()
        {
            string sql = "[dbo].[TestConnection]";
            using (SqlCommand cmd = new SqlCommand(sql, CurrentConnection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (CurrentConnection.State == ConnectionState.Closed)
                    CurrentConnection.Open();
                try
                {
                    var sqlTransaction = CurrentConnection.BeginTransaction();
                    cmd.Transaction = sqlTransaction;
                    cmd.ExecuteNonQuery();
                    //return;

                    sqlTransaction.Rollback();
                }
                finally
                {
                    //CurrentConnection.Close();
                }
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            SqlDependency.Stop(this.ConnectionString);
        }

        #endregion
    }
}